﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using ShopManagement.Common;
using ShopManagement.LynnShopDataSetTableAdapters;
using System.Data;

namespace ShopManagement.Dao
{
    public class SmDao
    {
        #region Attributes

        private OleDbConnection _myConnection;
        private OleDbCommand _myCommand;
        private string _query;
        private LynnShopDataSet _myDt = new LynnShopDataSet();

        #endregion

        #region Methods

        public SmDao(string connectionStr)
        {
            try
            {
                _myConnection = new OleDbConnection(connectionStr);
                _myConnection.Open();
                _myCommand = new OleDbCommand
                                 {
                                     Connection = _myConnection
                                 };
            }
            catch(Exception ex)
            {
                throw new Exception(Constants.Messages.LynnShopDbError, ex);
            }
        }

        public bool ValidateAccInfo(string acc, string pass)
        {
            int status = 0;
            _query = "Select password from Admin where account = '" + acc + "'";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                if (myReader.GetString(0) == pass)
                {
                    status = 1;
                }
            }
            myReader.Close();
            if(status == 1)
            {
                return true;
            }
            return false;
        }

        public int GetAccountRole(string acc)
        {
            int role = -1;
            _query = "Select role from Admin where account = '" + acc + "'";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                role = myReader.GetInt32(0);
            }
            return role;
        }

        public bool ValidateProductCode(string pdCode)
        {
            _query = "Select ProductCode from Products where ProductCode = '" + pdCode + "'";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            //0: true; 1: False
            int status = 1;
            while (myReader.Read())
            {
                status = 0;
            }
            myReader.Close();
            if(status == 0)
            {
                return true;
            }
            return false;
        }

        public void CloseConnection()
        {
            _myConnection.Close();
        }

        public LynnShopDataSet.ProductsDataTable GetProducts()
        {
            ProductsTableAdapter myPdAdapter = new ProductsTableAdapter();
            myPdAdapter.Connection = _myConnection;
            myPdAdapter.Fill(_myDt.Products);
            myPdAdapter.Dispose();
            return _myDt.Products;
        }

        public LynnShopDataSet.OrderDataTable GetOrders()
        {
            OrderTableAdapter myOdAdapter = new OrderTableAdapter();
            myOdAdapter.Connection = _myConnection;
            myOdAdapter.Fill(_myDt.Order);
            myOdAdapter.Dispose();
            return _myDt.Order;
        }

        public void InsertProduct(Product temp)
        {
            ProductsTableAdapter myPdAdapter = new ProductsTableAdapter();
            myPdAdapter.Connection = _myConnection;
            myPdAdapter.Insert(temp.ProductCode, temp.ProductName, temp.Price, temp.NetValue, temp.Image, temp.Quantity, 0, temp.Detail,temp.Type);
            myPdAdapter.Dispose();
        }

        public void InsertOrder(Order temp)
        {
            OrderTableAdapter myAdapter = new OrderTableAdapter();
            myAdapter.Connection = _myConnection;
            myAdapter.Insert(temp.CustomerName, temp.ProductId, temp.Quantity, temp.StartDate, temp.Status, temp.Detail,
                             temp.Profit, temp.EndDate);
            myAdapter.Dispose();
        }

        public void UpdateProduct(Product temp)
        {
            _query =
                "Update Products SET [ProductCode] = @Code , [ProductName] = @Name , [Price] = @Price , [NetValue] = @Net , [Image] = @Image , [Quantity] = @Quantity , [Sold] = @Sold , [Detail] = @Detail, [Type] = @Type Where Id =@Id ";
            _myCommand.CommandText = _query;
            _myCommand.Parameters.AddWithValue("@Code", temp.ProductCode);
            _myCommand.Parameters.AddWithValue("@Name", temp.ProductName);
            _myCommand.Parameters.AddWithValue("@Price", temp.Price);
            _myCommand.Parameters.AddWithValue("@Net", temp.NetValue);
            _myCommand.Parameters.AddWithValue("@Image", temp.Image);
            _myCommand.Parameters.AddWithValue("@Quantity", temp.Quantity);
            _myCommand.Parameters.AddWithValue("@Sold", temp.Sold);
            _myCommand.Parameters.AddWithValue("@Detail", temp.Detail);
            _myCommand.Parameters.AddWithValue("@Type", temp.Type);
            _myCommand.Parameters.AddWithValue("@Id", temp.Id);
            _myCommand.ExecuteNonQuery();
            _myCommand.Parameters.Clear();
        }

        public void UpdateOrder(Order temp)
        {
                _query =
                    "UPDATE [Order] SET [CustomerName]=?,[ProductId]=?,[Quantity]=?,[StartDate]=?,[Status]=?,[Detail]=?,[Profit]=?,[EndDate]=? WHERE [ID]=? ";
                _myCommand.CommandText = _query;
                _myCommand.Parameters.AddWithValue("@CustomerName", temp.CustomerName);
                _myCommand.Parameters.AddWithValue("@ProductId", temp.ProductId);
                _myCommand.Parameters.AddWithValue("@Quantity", temp.Quantity);
                _myCommand.Parameters.AddWithValue("@StartDate", temp.StartDate);
                _myCommand.Parameters.AddWithValue("@Status", temp.Status);
                _myCommand.Parameters.AddWithValue("@Detail", temp.Detail);
                _myCommand.Parameters.AddWithValue("@Profit", temp.Profit);
                _myCommand.Parameters.AddWithValue("@EndDate", temp.EndDate);
                _myCommand.Parameters.AddWithValue("@ID", temp.Id);
                _myCommand.ExecuteNonQuery();
                _myCommand.Parameters.Clear();
        }

        public string GetProductCode(string type)
        {
            string pdCode = "1";
            _query = "Select ProductCode from Products where ID in(Select Max(Id) from Products where type ='"+type+"')";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                pdCode = myReader.GetString(0);
            }
            myReader.Close();
            if(pdCode == "1")
            {
                return type + "0";
            }
            int number = int.Parse(pdCode.Substring(1));
            number += 1;
            return type + number;
        }

        public List<Product> SearchProduct(string text, int mode)
        {
            List<Product> results = new List<Product>();
            if(mode == Constants.SearchMode.Code)
            {
                _query = "Select * from Products where ProductCode like '%" + text + "%'";
            }
            if(mode == Constants.SearchMode.Name)
            {
                _query = "Select * from Products where ProductName like '%" + text + "%'";
            }
            if(mode == Constants.SearchMode.Price)
            {
                _query = "Select * from Products where Price > " + text;
            }
            return results;
        }

        public Product GetAProduct(string pdCode)
        {
            Product myPd = new Product();
            _query = "Select * from Products where ProductCode = '" + pdCode + "'";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                myPd.Id = myReader.GetInt32(0);
                myPd.ProductCode = myReader.GetString(1);
                myPd.ProductName = myReader.GetString(2);
                myPd.Price = myReader.GetInt32(3);
                myPd.NetValue = myReader.GetInt32(4);
                myPd.Image = myReader.GetString(5);
                myPd.Quantity = myReader.GetInt32(6);
                myPd.Sold = myReader.GetInt32(7);
                myPd.Detail = myReader.GetString(8);
                myPd.Type = myReader.GetString(9);
            }
            myReader.Close();
            return myPd;
        }

        public Order GetAOrder(int orderNo)
        {
            Order myOrder = new Order();
            _query = "Select * from [Order] where [ID] = " + orderNo+" ;";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                myOrder.Id = myReader.GetInt32(0);
                myOrder.CustomerName = myReader.GetString(1);
                myOrder.ProductId = myReader.GetString(2);
                myOrder.Quantity = myReader.GetInt32(3);
                myOrder.StartDate = myReader.GetDateTime(4);
                myOrder.Status = myReader.GetString(5);
                myOrder.Detail = myReader.GetString(6);
                myOrder.Profit = myReader.GetInt32(7);
                myOrder.EndDate = myReader.GetDateTime(8);
            }
            myReader.Close();
            return myOrder;

        }

        public void DeleteProducts(List<string> pdCodes)
        {
            _query = "Delete from Products Where ProductCode in (";
            foreach (var pdCode in pdCodes)
            {
                _query += "'" + pdCode + "',";
            }
            _query = _query.Remove(_query.Length - 1);
            _query += ")";
            _myCommand.CommandText = _query;
            _myCommand.ExecuteNonQuery();
        }

        public void DeleteOrders(int orderCodes)
        {
            _query = "Delete from [Order] Where Id = " + orderCodes;
            _myCommand.CommandText = _query;
            _myCommand.ExecuteNonQuery();
        }

        public List<Product> GetRemainProducts(ref DataSet tempDt)
        {
            List<Product> rtList = new List<Product>();
            _query = "Select * from Products where Quantity > Sold";
            _myCommand.CommandText = _query;
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            DataTable temp = new DataTable("Products");
            tempDt.Tables.Add(temp);
            tempDt.Load(myReader, LoadOption.PreserveChanges, tempDt.Tables[0]);
            myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                Product myPd =new Product();
                myPd.Id = myReader.GetInt32(0);
                myPd.ProductCode = myReader.GetString(1);
                myPd.ProductName = myReader.GetString(2);
                myPd.Price = myReader.GetInt32(3);
                myPd.NetValue = myReader.GetInt32(4);
                myPd.Image = myReader.GetString(5);
                myPd.Quantity = myReader.GetInt32(6);
                myPd.Sold = myReader.GetInt32(7);
                myPd.Detail = myReader.GetString(8);
                myPd.Type = myReader.GetString(9);
                rtList.Add(myPd);
            }
            myReader.Close();
            return rtList;
        } 

        public List<Order> GetOrdersWithTimeConditions(DateTime from, DateTime to, ref DataSet tempDt)
        {
            List<Order> rtList = new List<Order>();
            _query = "Select * from [Order] where [Status]=@Status And [EndDate]>=@From ANd [EndDate]<=@To ";
            _myCommand.CommandText = _query;
            _myCommand.Parameters.AddWithValue("@Status", Constants.OrderStatus.Done);
            _myCommand.Parameters.AddWithValue("@From", from);
            _myCommand.Parameters.AddWithValue("@To", to);
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            DataTable temp = new DataTable("Orders");
            tempDt.Tables.Add(temp);
            tempDt.Load(myReader, LoadOption.PreserveChanges, tempDt.Tables[0]);
            myReader = _myCommand.ExecuteReader();
            while (myReader.Read())
            {
                Order myOrder = new Order();
                myOrder.Id = myReader.GetInt32(0);
                myOrder.CustomerName = myReader.GetString(1);
                myOrder.ProductId = myReader.GetString(2);
                myOrder.Quantity = myReader.GetInt32(3);
                myOrder.StartDate = myReader.GetDateTime(4);
                myOrder.Status = myReader.GetString(5);
                myOrder.Detail = myReader.GetString(6);
                myOrder.Profit = myReader.GetInt32(7);
                myOrder.EndDate = myReader.GetDateTime(8);
                rtList.Add(myOrder);
            }
            
            myReader.Close();
            _myCommand.Parameters.Clear();
            return rtList;
        }
 
        public DataSet GetOrderWithStatus(string status)
        {
            DataSet tempDt = new DataSet();
            _query = "Select * from [Order] where [Status]=?";
            _myCommand.CommandText = _query;
            _myCommand.Parameters.AddWithValue("@Status", status);
            OleDbDataReader myReader = _myCommand.ExecuteReader();
            DataTable temp = new DataTable("Orders");
            tempDt.Tables.Add(temp);
            tempDt.Load(myReader, LoadOption.PreserveChanges, tempDt.Tables[0]);
            myReader.Close();
            _myCommand.Parameters.Clear();
            return tempDt;
        }

        #endregion
    }
}